/**
 * 
 */
package com.scpii.universal.pns.dao.impl;

import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.scpii.universal.constant.GenericConstants;
import com.scpii.universal.hibernate.dao.impl.BaseDaoImpl;
import com.scpii.universal.pns.dao.UsersDao;
import com.scpii.universal.pns.dto.ClientUserDto;
import com.scpii.universal.pns.entity.Users;

/**
 * genericWeb
 * 
 * @author acer 2012-7-5下午04:35:11
 */
@Repository
public class UsersDaoImpl extends BaseDaoImpl<Users, Long> implements UsersDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getClientUserInit(Long appId,
			ClientUserDto condition, Integer pageNo, Integer pageSize) {
		String schema = ResourceBundle.getBundle(
				GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault())
				.getString("pns.schema");
		StringBuilder userSql = new StringBuilder(
				MessageFormat
						.format("SELECT DISTINCT u.\"ID\" AS DID ,u.* FROM {0}.\"USERS\" u   "
								+ "LEFT JOIN {0}.USER_DEVICES ud ON UD.USER_ID=u.\"ID\" "
								+ "LEFT JOIN {0}.DEVICES d ON d.ID=ud.DEVICE_ID WHERE u.APP_ID=:appId ",
								schema));
		String userLinkSql = MessageFormat
				.format("SELECT ul.* FROM {0}.\"USER_LINKS\" ul WHERE ul.USER_ID=:userId ",
						schema);
		String devicesSql = MessageFormat
				.format("SELECT d.* FROM {0}.\"DEVICES\" d INNER JOIN {0}.USER_DEVICES ud ON UD.DEVICE_ID=d.\"ID\" WHERE UD.USER_ID=:userId AND ud.status=1 ",
						schema);
		String stateSql = MessageFormat
				.format("SELECT UAS.\"COUNT\",UAS.ACTION_TYPE FROM {0}.\"USER_ACTION_STATE\" uas  WHERE UAS.USER_ID=:userId ",
						schema);

		String orderSql = MessageFormat
				.format("SELECT COUNT(1) AS COUNT FROM {0}.\"EC_ORDERS\" ec WHERE USER_ID=:userId ",
						schema);
		if (condition != null) {
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					|| !StringUtils.isBlank(condition.getDeviceType())) {
				userSql.append("AND ud.status=1 ");
			}
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					&& !"-全部-".equals(condition.getDeviceModel())) {
				String[] models = condition.getDeviceModel();
				StringBuffer sb = new StringBuffer();
				for (int i = 0; i < models.length; i++) {
					if (i != 0) {
						sb.append(",");
					}
					sb.append("'" + models[i] + "'");
				}
				userSql.append("AND d.DEVICE_MODEL IN (" + sb.toString() + ") ");
			}
			if (!StringUtils.isBlank(condition.getDeviceType())) {
				userSql.append("AND LOWER(d.DEVICE_TYPE) = LOWER('"
						+ condition.getDeviceType() + "') ");
			}
			if (!StringUtils.isBlank(condition.getUserName())
					&& !"昵称/用户名".equals(condition.getUserName())) {
				userSql.append("AND (u.USER_NAME LIKE '%"
						+ condition.getUserName()
						+ "%' OR u.USER_AILAS LIKE '%"
						+ condition.getUserName() + "%') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtStart())) {
				userSql.append("AND u.LAST_ACTIVITY_DT >= TO_DATE('"
						+ condition.getLastActivityDtStart()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtEnd())) {
				userSql.append("AND u.LAST_ACTIVITY_DT <= TO_DATE('"
						+ condition.getLastActivityDtEnd()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getUserType())) {
				userSql.append("AND u.USER_TYPE='" + condition.getUserType()
						+ "'");
			}
			if (!StringUtils.isBlank(condition.getProvider())) {
				userSql.append("AND u.PROVIDER='" + condition.getProvider()
						+ "'");
			}
			if (condition.getUserIds() != null
					&& condition.getUserIds().size() != 0) {
				userSql.append("AND u.ID IN ('"
						+ StringUtils.join(condition.getUserIds(), "','")
						+ "')");
			}
		}
		userSql.append("ORDER BY u.LAST_ACTIVITY_DT DESC, u.USER_TYPE DESC,u.ID DESC ");
		SQLQuery userSqlQuery = getSession().createSQLQuery(userSql.toString());
		if (pageNo != 0 && pageSize != 0) {
			userSqlQuery.setFirstResult((pageNo - 1) * pageSize);
			userSqlQuery.setMaxResults(pageSize);
		}
		userSqlQuery.setParameter("appId", appId);
		userSqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> users = userSqlQuery.list();

		String imgPath = ResourceBundle.getBundle(
				GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault())
				.getString("pns.img.path");
		if (users != null) {
			for (Map<String, Object> user : users) {
				if (null != user.get("AVATAR")
						&& !StringUtils.contains((String) user.get("AVATAR"),
								"http://")) {
					user.put("AVATAR", imgPath + (String) user.get("AVATAR"));
				}
				SQLQuery linkQuery = getSession().createSQLQuery(userLinkSql);
				linkQuery.setParameter("userId", user.get("ID"));
				linkQuery
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List<Map<String, Object>> links = linkQuery.list();
				user.put("LINKS", links);
				SQLQuery deviceQuery = getSession().createSQLQuery(devicesSql);
				deviceQuery.setParameter("userId", user.get("ID"));
				deviceQuery
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List<Map<String, Object>> devices = deviceQuery.list();
				user.put("DEVICES", devices);

				SQLQuery stateQuery = getSession().createSQLQuery(stateSql);
				stateQuery.setParameter("userId", user.get("ID"));
				stateQuery
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List<Map<String, Object>> states = stateQuery.list();
				for (Map<String, Object> state : states) {
					user.put((String) state.get("ACTION_TYPE") + "_COUNT",
							state.get("COUNT"));
				}
				SQLQuery orderQuery = getSession().createSQLQuery(orderSql);
				orderQuery.setParameter("userId", user.get("ID"));
				user.put("EC_PRODUCT_ADD_COUNT",
						((BigDecimal) orderQuery.uniqueResult()).intValue());
			}
		}
		return users;
	}

	@Override
	public Integer getClientUserInitCount(Long appId, ClientUserDto condition) {
		String schema = ResourceBundle.getBundle(
				GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault())
				.getString("pns.schema");
		StringBuilder sql = new StringBuilder(
				MessageFormat
						.format("SELECT \"COUNT\"(1) FROM (SELECT DISTINCT u.\"ID\" AS DID FROM {0}.\"USERS\" u   "
								+ "LEFT JOIN {0}.USER_DEVICES ud ON UD.USER_ID=u.\"ID\" "
								+ "LEFT JOIN {0}.DEVICES d ON d.ID=ud.DEVICE_ID WHERE u.APP_ID=:appId ",
								schema));
		if (condition != null) {
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					|| !StringUtils.isBlank(condition.getDeviceType())) {
				sql.append("AND ud.status=1 ");
			}
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					&& !"-全部-".equals(condition.getDeviceModel())) {
				String[] models = condition.getDeviceModel();
				StringBuffer sb = new StringBuffer();
				for (int i = 0; i < models.length; i++) {
					if (i != 0) {
						sb.append(",");
					}
					sb.append("'" + models[i] + "'");
				}
				sql.append("AND d.DEVICE_MODEL IN (" + sb.toString() + ") ");
			}
			if (!StringUtils.isBlank(condition.getDeviceType())) {
				sql.append("AND LOWER(d.DEVICE_TYPE) = LOWER('"
						+ condition.getDeviceType() + "') ");
			}
			if (!StringUtils.isBlank(condition.getUserName())
					&& !"昵称/用户名".equals(condition.getUserName())) {
				sql.append("AND (u.USER_NAME LIKE '%" + condition.getUserName()
						+ "%' OR u.USER_AILAS LIKE '%"
						+ condition.getUserName() + "%') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtStart())) {
				sql.append("AND u.LAST_ACTIVITY_DT >= TO_DATE('"
						+ condition.getLastActivityDtStart()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtEnd())) {
				sql.append("AND u.LAST_ACTIVITY_DT <= TO_DATE('"
						+ condition.getLastActivityDtEnd()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getUserType())) {
				sql.append("AND u.USER_TYPE='" + condition.getUserType() + "'");
			}
			if (!StringUtils.isBlank(condition.getProvider())) {
				sql.append("AND u.PROVIDER='" + condition.getProvider() + "'");
			}
			if (condition.getUserIds() != null
					&& condition.getUserIds().size() != 0) {
				sql.append("AND u.ID IN ('"
						+ StringUtils.join(condition.getUserIds(), "','")
						+ "')");
			}
		}
		sql.append(") ");
		SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString());
		sqlQuery.setParameter("appId", appId);

		return ((BigDecimal) sqlQuery.uniqueResult()).intValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getUserIds(long appId,
			ClientUserDto condition) {
		String schema = ResourceBundle.getBundle(
				GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault())
				.getString("pns.schema");
		StringBuilder userSql = new StringBuilder(
				MessageFormat
						.format("SELECT DISTINCT u.\"ID\" AS ID FROM {0}.\"USERS\" u   "
								+ "LEFT JOIN {0}.USER_DEVICES ud ON UD.USER_ID=u.\"ID\"  "
								+ "LEFT JOIN {0}.DEVICES d ON d.ID=ud.DEVICE_ID WHERE u.APP_ID=:appId ",
								schema));
		if (condition != null) {
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					|| !StringUtils.isBlank(condition.getDeviceType())) {
				userSql.append("AND ud.status=1 ");
			}
			if (!ArrayUtils.isEmpty(condition.getDeviceModel())
					&& !"-全部-".equals(condition.getDeviceModel())) {
				String[] models = condition.getDeviceModel();
				StringBuffer sb = new StringBuffer();
				for (int i = 0; i < models.length; i++) {
					if (i != 0) {
						sb.append(",");
					}
					sb.append("'" + models[i] + "'");
				}
				userSql.append("AND d.DEVICE_MODEL IN (" + sb.toString() + ") ");
			}
			if (!StringUtils.isBlank(condition.getDeviceType())) {
				userSql.append("AND LOWER(d.DEVICE_TYPE) = LOWER('"
						+ condition.getDeviceType() + "') ");
			}
			if (!StringUtils.isBlank(condition.getUserName())
					&& !"昵称/用户名".equals(condition.getUserName())) {
				userSql.append("AND (u.USER_NAME LIKE '%"
						+ condition.getUserName()
						+ "%' OR u.USER_AILAS LIKE '%"
						+ condition.getUserName() + "%') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtStart())) {
				userSql.append("AND u.LAST_ACTIVITY_DT >= TO_DATE('"
						+ condition.getLastActivityDtStart()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getLastActivityDtEnd())) {
				userSql.append("AND u.LAST_ACTIVITY_DT <= TO_DATE('"
						+ condition.getLastActivityDtEnd()
						+ "','yyyy-mm-dd hh24:mi:ss') ");
			}
			if (!StringUtils.isBlank(condition.getUserType())) {
				userSql.append("AND u.USER_TYPE='" + condition.getUserType()
						+ "'");
			}
			if (!StringUtils.isBlank(condition.getProvider())) {
				userSql.append("AND u.PROVIDER='" + condition.getProvider()
						+ "'");
			}

			if (condition.getUserIds() != null
					&& condition.getUserIds().size() != 0) {
				userSql.append("AND u.ID IN ('"
						+ StringUtils.join(condition.getUserIds(), "','")
						+ "')");
			}
		}
		SQLQuery sqlQuery = getSession().createSQLQuery(userSql.toString());
		sqlQuery.setParameter("appId", appId);
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public String[] getUserNamesByIds(String[] userIds) {
		String schema = ResourceBundle.getBundle(
				GenericConstants.GLOBAL_PROPERTIES_PATH, Locale.getDefault())
				.getString("pns.schema");
		String sql = MessageFormat.format(
				"SELECT u.USER_AILAS FROM {0}.USERS u WHERE ID IN('"
						+ StringUtils.join(userIds, "','") + "')", schema);
		SQLQuery query = getSession().createSQLQuery(sql);
		List<Object> results = query.list();
		String[] strArry = new String[results.size()];
		for (int i = 0; i < results.size(); i++) {
			strArry[i] = (String) results.get(i);
		}
		return strArry;
	}

	@Override
	public Users getUserByUserName(String userName, long appId) {
		String hql = "FROM Users u WHERE u.userName=:userName AND u.appId=:appId";
		Query query = getSession().createQuery(hql);
		query.setParameter("userName", userName);
		query.setParameter("appId", appId);
		return (Users) query.uniqueResult();
	}
}
